<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.util.*,java.sql.*,com.kylin.hibernate.*"%>
<%
String selectBadProductDict="select * from DICTS where FKP_DICT_ID not in (select PK_DICT_ID from DICTS)";
String selectBadCatDict="select * from DICTS where FK_CAT_DICT_ID not in (select PK_CAT_DICT_ID from CAT_DICTS)";
String selectBadProduct="select * from PRODUCTS where "
   						+ "   (FK_UNIT_ID not in (select PK_DICT_ID from DICTS) ) "
     					+ "or (FK_CAT_PRODUCT_ID not in (select PK_DICT_ID from DICTS)) "
						+ "or (FK_CURRENCY_ID not in (select PK_DICT_ID from DICTS) )";
String selectBadContent="select * from CONTENTS where "
   						+ "   (FK_UNIT_ID not in (select PK_DICT_ID from DICTS) ) "
     					+ "or (FK_CAT_PRODUCT_ID not in (select PK_DICT_ID from DICTS)) "
						+ "or (FK_CURRENCY_ID not in (select PK_DICT_ID from DICTS) )";

String selectBadCompany_cat="select * from COMPANY_CATS where FK_CAT_PRODUCT_ID not in (select PK_DICT_ID from DICTS)";
String selectBadCompany_ind="select * from COMPANY_INDS where FK_INDUSTRY_ID not in (select PK_DICT_ID from DICTS)";


String selectBadVocabulary="select * from VOCABULARYS where FK_CAT_VOCABULARY_ID not in (select PK_DICT_ID from DICTS)";
String selectBadArticle="select * from ARTICLES where FK_CAT_ARTICLE_ID not in (select PK_DICT_ID from DICTS)";
String selectBadCourseware="select * from COURSEWARES where FK_CAT_COURSEWARE_ID not in (select PK_DICT_ID from DICTS)"
                          +" or FK_ARTICLE_COURSEWARE_ID not in (select PK_DICT_ID from DICTS)";
     
String selectBadSpecial="select * from SPECIALS where FK_CAT_SPECIAL_ID not in (select PK_DICT_ID from DICTS)";

  	out.println("<br>  查看损坏的数据 <hr>");
	Connection con=null;
	try {
		con = BaseHibernateDAO.getSession().connection();
		PreparedStatement pstat = null;
		
		pstat = con.prepareStatement(selectBadProductDict);
		List ids = new ArrayList();
		out.println( getRsStr( pstat.executeQuery(), "产品类别",ids));
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from DICTS where PK_DICT_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadCatDict);
		out.println( getRsStr( pstat.executeQuery(), "所有字典",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from DICTS where PK_DICT_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadProduct);
		out.println( getRsStr( pstat.executeQuery(), "产品",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from PRODUCTS where PK_PRODUCT_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadContent);
		out.println( getRsStr( pstat.executeQuery(), "贸易信息",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from CONTENTS where PK_CONTENT_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadCompany_cat);
		out.println( getRsStr( pstat.executeQuery(), "公司行业",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from COMPANY_CATS where PK_COMPANY_CAT_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadCompany_ind);
		out.println( getRsStr( pstat.executeQuery(), "公司主营",ids) );
		out.println( delBadData(con,ids,"delete from COMPANY_INDS where PK_COMPANY_IND_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadVocabulary);
		out.println( getRsStr( pstat.executeQuery(), "词汇",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from VOCABULARYS where PK_VOCABULARY_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadArticle);
		out.println( getRsStr( pstat.executeQuery(), "阅读文章",ids) );
		out.println( delBadData(con,ids,"delete from ARTICLES where PK_ARTICLE_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadCourseware);
		out.println( getRsStr( pstat.executeQuery(), "课件",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from COURSEWARES where PK_COURSEWARE_ID in"));
		
		ids = new ArrayList();
		pstat = con.prepareStatement(selectBadSpecial);
		out.println( getRsStr( pstat.executeQuery(), "专题",ids) );
		if(request.getParameter("delete") != null)
		out.println( delBadData(con,ids,"delete from SPECIALS where PK_SPECIAL_ID in"));
   
	} catch (SQLException e) {
		out.print("联接数据库出错：" + e);
	} finally {
		//不管对错都要执行的代码 
		try {
			con.close();
		} catch (Exception xx) {

		}
	}
%>

<%!
    public String getRsStr(ResultSet rs,String dataName,List ids) throws SQLException{
		StringBuffer sb = new StringBuffer("<br><table border=1>");
		int all = 0;
		while (rs.next()) {
			all++;
			try{
			ids.add(rs.getString(1));
			sb.append("<tr><td>&nbsp;"+rs.getString(1)+"</td>");
			sb.append("<td>&nbsp;" + rs.getString(2)+"</td>");
			sb.append("<td>&nbsp;" + rs.getString(3)+"</td>");
			sb.append("<td>&nbsp;" + rs.getString(4)+"</td>");
			sb.append("<td>&nbsp;" + rs.getString(5)+"</td></tr>");
			}catch(Exception e){sb.append("<td>"+e+"</td>");}
		}
		sb.append("<tr><td colspan=5>查询到 " + all + " 条坏的 ‘"+dataName+"’ 数据</td></tr></table>");
        return sb.toString();
    }
    
    public String delBadData(Connection con,List ids,String delSql) {
    	String rsStr ="";
    	int rs = 0;
    	try{
    	if(ids.size()>0){
    	delSql +="(";
    	for(int i=0;i<ids.size();i++){
    	    if(i!=0)
    	    	delSql +=",";
    		delSql +=ids.get(i);
    	}
    	delSql +=")";
        PreparedStatement pstat = con.prepareStatement(delSql);
        rs = pstat.executeUpdate();
        rsStr= "删除"+rs+" rows";
        pstat.close(); 
        }
        }catch(SQLException e){
        rsStr ="删除 {"+delSql+"} ERROR : "+e;
            e.printStackTrace();
        }
        return rsStr;
    }
 %>
